const db = require('../../database/connection');

class UserModel {
  constructor() {
    this.tableName = 'users';
  }

  /**
   * Create a new user
   */
  async create(userData) {
    const [user] = await db(this.tableName)
      .insert(userData)
      .returning('*');
    return user;
  }

  /**
   * Find user by ID
   */
  async findById(id) {
    return await db(this.tableName)
      .where({ id })
      .whereNull('deleted_at')
      .first();
  }

  /**
   * Find user by email
   */
  async findByEmail(email) {
    return await db(this.tableName)
      .where({ email })
      .whereNull('deleted_at')
      .first();
  }

  /**
   * Find user by username
   */
  async findByUsername(username) {
    return await db(this.tableName)
      .where({ username })
      .whereNull('deleted_at')
      .first();
  }

  /**
   * Get all users with pagination and filtering
   */
  async findAll(filters = {}, pagination = {}) {
    const { page = 1, limit = 10 } = pagination;
    const { status, role, search } = filters;
    
    let query = db(this.tableName)
      .whereNull('deleted_at');

    // Apply filters
    if (status) {
      query = query.where('status', status);
    }
    
    if (role) {
      query = query.where('role', role);
    }
    
    if (search) {
      query = query.where(function() {
        this.where('username', 'like', `%${search}%`)
          .orWhere('email', 'like', `%${search}%`)
          .orWhere('first_name', 'like', `%${search}%`)
          .orWhere('last_name', 'like', `%${search}%`);
      });
    }

    // Get total count
    const totalQuery = query.clone();
    const [{ count }] = await totalQuery.count('id as count');
    
    // Apply pagination
    const offset = (page - 1) * limit;
    const users = await query
      .select(['id', 'username', 'email', 'first_name', 'last_name', 'role', 'status', 'created_at', 'updated_at'])
      .limit(limit)
      .offset(offset)
      .orderBy('created_at', 'desc');

    return {
      data: users,
      pagination: {
        total: parseInt(count, 10),
        page: parseInt(page, 10),
        limit: parseInt(limit, 10),
        totalPages: Math.ceil(count / limit),
      },
    };
  }

  /**
   * Update user by ID
   */
  async updateById(id, updateData) {
    const [updatedUser] = await db(this.tableName)
      .where({ id })
      .whereNull('deleted_at')
      .update({
        ...updateData,
        updated_at: new Date(),
      })
      .returning('*');
    
    return updatedUser;
  }

  /**
   * Soft delete user by ID
   */
  async deleteById(id) {
    const [deletedUser] = await db(this.tableName)
      .where({ id })
      .whereNull('deleted_at')
      .update({
        deleted_at: new Date(),
      })
      .returning('*');
    
    return deletedUser;
  }

  /**
   * Update last login timestamp
   */
  async updateLastLogin(id) {
    return await db(this.tableName)
      .where({ id })
      .update({
        last_login_at: new Date(),
        updated_at: new Date(),
      });
  }

  /**
   * Check if email exists
   */
  async emailExists(email, excludeId = null) {
    let query = db(this.tableName)
      .where({ email })
      .whereNull('deleted_at');
    
    if (excludeId) {
      query = query.whereNot('id', excludeId);
    }
    
    const user = await query.first();
    return !!user;
  }

  /**
   * Check if username exists
   */
  async usernameExists(username, excludeId = null) {
    let query = db(this.tableName)
      .where({ username })
      .whereNull('deleted_at');
    
    if (excludeId) {
      query = query.whereNot('id', excludeId);
    }
    
    const user = await query.first();
    return !!user;
  }
}

module.exports = new UserModel();